package com.shengruan.user.jdbc;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.shengruan.admin.vo.Question;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.shengruan.framework.jdbc.BaseJdbc;
import com.shengruan.framework.jdbc.BeanHandler;
import com.shengruan.framework.jdbc.BeanListHandler;
import com.shengruan.user.vo.QuestionAnswer;

public class QuestionAnswerJdbc extends BaseJdbc {

    Logger logger = LoggerFactory.getLogger(getClass());

    public static final String QUESTIONANSWER_GETBYID = "select * from question_answer q where q.id = ?";


    public QuestionAnswer getByQuestionAndMobile(int questionId, String mobile) {
        Connection conn = this.getConnection();
        String sql = "SELECT qa.* FROM users u LEFT JOIN question_answer qa ON u.id = qa.userId WHERE u.mobile = ? AND qa.questionId = ?";
        QuestionAnswer questionAnswer = null;
        try {
            questionAnswer = this.query(conn, sql, new BeanHandler<QuestionAnswer>(QuestionAnswer.class), new Object[]{mobile, questionId});
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(conn);
        }
        return questionAnswer;
    }

    public QuestionAnswer getByQuestionAndEmail(int questionId, String email) {
        Connection conn = this.getConnection();
        String sql = "SELECT qa.* FROM users u LEFT JOIN question_answer qa ON u.id = qa.userId WHERE u.email = ? AND qa.questionId = ?";
        QuestionAnswer questionAnswer = null;
        try {
            questionAnswer = this.query(conn, sql, new BeanHandler<QuestionAnswer>(QuestionAnswer.class), new Object[]{email, questionId});
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(conn);
        }
        return questionAnswer;
    }


    public List<Question> loadAll(int userId) {
        List<Question> questionList = new ArrayList<Question>();
        Connection conn = this.getConnection();
        StringBuilder sql = new StringBuilder("select * from question q ,question_answer qa WHERE q.id = qa.questionId and qa.userId = ? order by qa.creationTime asc");
        Object[] pram = {userId};
        try
		{
			questionList = this.query(conn, sql.toString(), new BeanListHandler<Question>(Question.class), pram);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close(conn);
		}
		return questionList;
    }


    public QuestionAnswer getById(int id) {
        Connection conn = this.getConnection();
        QuestionAnswer questionAnswer = null;
        try {
            questionAnswer = this.query(conn, QUESTIONANSWER_GETBYID, new BeanHandler<QuestionAnswer>(QuestionAnswer.class), id);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.close(conn);
		}
		return questionAnswer;
	}
}
